package com.magicalcoder.youyamvc.core.common.utils;

import com.alibaba.fastjson.JSON;
import com.magicalcoder.youyamvc.core.common.dto.PageDto;
import com.magicalcoder.youyamvc.core.common.utils.date.DateFormatUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.*;

/**
 * Created by hzhedongyu on 2016/8/12.
 */
public class POIUtil<T>{

    /**
     * 设置某些列的值只能输入预制的数据,显示下拉框.
     * @param sheet 要设置的sheet.
     * @return 设置好的sheet.
     */
    public static <T> Sheet setValidation(List<T> pageList,String[] mappingArr,Sheet sheet) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        CellRangeAddressList addressList = new CellRangeAddressList();
        int size = pageList.size();
        for(int i=0;i<mappingArr.length;i++){
            if(StringUtils.isNotBlank(mappingArr[i])){
                String[] selectList = mappingToSelectList(mappingArr[i]);
                if(selectList!=null && selectList.length>0){
                    addressList.addCellRangeAddress(2, i,size+1, i);
                    //设置下拉框数据
                    DataValidationConstraint constraint = helper.createExplicitListConstraint(mappingToSelectList(mappingArr[i]));
                    DataValidation dataValidation = helper.createValidation(constraint, addressList);
                    //处理Excel兼容性问题
                    if(dataValidation instanceof XSSFDataValidation) {
                        dataValidation.setSuppressDropDownArrow(true);
                        dataValidation.setShowErrorBox(true);
                    }else {
                        dataValidation.setSuppressDropDownArrow(false);
                    }
                    sheet.addValidationData(dataValidation);
                }
            }
        }
        return sheet;
    }

    private static String[] mappingToSelectList(String mapping){
        List<Map<String,String>> list = null;
        try {
            list = (List<Map<String,String>>)JSON.parse(mapping);
        }catch (Exception e){
            e.printStackTrace();
        }
        if(ListUtils.isBlank(list)){
            return null;
        }
        //过滤空选项
        for(int i=0;i<list.size();i++){
            Map<String,String> itemMap = list.get(i);
            if(itemMap!=null && !itemMap.isEmpty()){
                Set<String> keySet = itemMap.keySet();
                for(String key:keySet){
                    if(StringUtils.isBlank(key)){
                        list.remove(i);
                        i--;
                    }
                }
            }
        }
        String[] selectList = new String[list.size()];
        if(ListUtils.isNotBlank(list)){
            for(int i=0;i<list.size();i++){
                Map<String,String> itemMap = list.get(i);
                if(itemMap!=null && !itemMap.isEmpty()){
                    Set<String> keySet = itemMap.keySet();
                    for(String key:keySet){
                        if(StringUtils.isNotBlank(key)){
                            selectList[i]=itemMap.get(key);
                        }
                    }
                }
            }
        }
        return selectList;
    }

    public static void main(String[] args) throws FileNotFoundException {
        List<PageDto> mapDtoList = new ArrayList<PageDto>();
        mapDtoList.add(new PageDto(1,1,1L,"a"));
        mapDtoList.add(new PageDto(2,2,2L,"b"));
        mapDtoList.add(new PageDto(3,3,3L,"c"));
        mapDtoList.add(new PageDto(4,4,4L,"d"));
        String excelFile = "C:\\Users\\hzhedongyu\\Downloads\\b.xlsx";
        POIUtil<PageDto> poiUtil = new POIUtil<PageDto>();
//        poiUtil.writeExcelToTempFile(mapDtoList,new String[]{"pageSize","pageNumber","totalSize","orderBy"},new String[]{"[{\"\":\"请选择\"},{\"1\":\"类型1\"},{\"2\":\"类型2\"}]","[{\"\":\"请选择\"},{\"1\":\"类型1\"},{\"2\":\"类型2\"}]"},new File(excelFile));
        List<PageDto> pageDtoList = poiUtil.readExcel(new FileInputStream(new File(excelFile)),PageDto.class);
        System.out.println(JSON.toJSONString(pageDtoList));
    }

    /**
     *
     * @param pageList 集合对象
     * @param titles   第一行标题
     * @param mappingArr  映射：[{"1":"类型1","2":"类型2"}]
     * @param tmpFile  导出的excel文件
     */
    public void writeExcelToTempFile(List<T> pageList, String[] titles, String[] mappingArr, File tmpFile) {
        if(mappingArr!=null && mappingArr.length>0){//过滤非法数据 必须是jjson数组数据
            for(int i=0;i<mappingArr.length;i++){
                try {
                    List<Map<String,String>> list = (List<Map<String,String>>)JSON.parse(mappingArr[i]);
                }catch (Exception e){
                    mappingArr[i]="";
                }
            }
        }

        FileOutputStream out = null;
        SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
        Sheet sh = wb.createSheet();
        try {
            setValidation(pageList, mappingArr,sh);
            //头部 第1行
            Row headRow = sh.createRow(0);
            for(int i=0;i<titles.length;i++){
                Cell cell = headRow.createCell(i);
                cell.setCellValue(titles[i]);
            }
            //头部 第2行
            Row headSubRow = sh.createRow(1);
            for(int i = 0; i< mappingArr.length; i++){
                Cell cell = headSubRow.createCell(i);
                cell.setCellValue(mappingArr[i]);
            }
            Map<Integer,Map<String,String>> mappingMap = buildMappingMergeMap(mappingArr);

            //数据
            for(int i=1;i<=pageList.size();i++){
                Object obj = pageList.get(i-1);
                Row row = sh.createRow(i+1);
                Field[] fields=obj.getClass().getDeclaredFields();
                for(int j=0;j<fields.length;j++){
                    Map<String,String> map = mappingMap.get(j);
                    Field field = fields[j];
                    Cell cell = row.createCell(j);
                    field.setAccessible(true);
                    Object dbValue = field.get(obj);
                    String magicalValue = map!=null?map.get(String.valueOf(dbValue)):null;
                    if(dbValue != null){
                        String type = field.getType().getSimpleName();
                        if(type.equals("Date")){
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            magicalValue = DateFormatUtils.getStringDate((Date)dbValue,"yyyy-MM-dd HH:mm:ss");
                            cell.setCellValue(magicalValue);
                        }else if(type.equals("Boolean") || type.equals("boolean")){
                            if(magicalValue!=null){
                                cell.setCellValue(magicalValue);
                            }else {
                                cell.setCellValue(String.valueOf(dbValue));
                            }
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                        }else if(type.equals("Long")|| type.equals("long")){
                            if(magicalValue!=null){
                                cell.setCellValue(magicalValue);
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                            }else {
                                cell.setCellValue((Long)(dbValue));
                                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            }
                        }else if(type.equals("Integer")|| type.equals("int")){
                            if(magicalValue!=null){
                                cell.setCellValue(magicalValue);
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                            }else {
                                cell.setCellValue((Integer)dbValue);
                                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            }
                        }else if(type.equals("Double")|| type.equals("double")){
                            if(magicalValue!=null){
                                cell.setCellValue(magicalValue);
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                            }else {
                                cell.setCellValue((Double)dbValue);
                                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            }
                        }else if(type.equals("Float")|| type.equals("float")){
                            if(magicalValue!=null){
                                cell.setCellValue(magicalValue);
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                            }else {
                                cell.setCellValue((Float)dbValue);
                                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            }
                        }else if(type.equals("BigDecimal")){
                            if(magicalValue!=null){
                                cell.setCellValue(magicalValue);
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                            }else {
                                cell.setCellValue(((BigDecimal)dbValue).floatValue());
                                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            }
                        }else {
                            if(magicalValue!=null){
                                cell.setCellValue(magicalValue);
                            }else {
                                cell.setCellValue(String.valueOf(dbValue));
                            }
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                        }

                    }
                }
            }
            out = new FileOutputStream(tmpFile);
            wb.write(out);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(out!=null){
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            if(wb!=null){
                wb.dispose();
            }
        }
    }



    private String[] rowToMapping(Row mappingRow){
        int columnsNum = mappingRow.getLastCellNum();//获取列数
        String[] mappingArr = new String[columnsNum+1];
        for(int i=0;i<columnsNum;i++){
            Cell cell = mappingRow.getCell(i);
            if(cell!=null){
                mappingArr[i] = cell.getStringCellValue();
            }
        }
        return mappingArr;
    }

    public List<T> readExcel(InputStream inputStream,Class<T> clazz) {
        List<T> list = new ArrayList<T>();
        try {
            XSSFWorkbook wb = new XSSFWorkbook(new BufferedInputStream(inputStream));
            Sheet sheet = wb.getSheetAt(0);  //获取 某个表

            Row mappingRow = sheet.getRow(1);
            String[] mappingArr = rowToMapping(mappingRow);
            Map<Integer,Map<String,String>> mappingMap = buildMappingReverseMap(mappingArr);

            int rowNum = sheet.getLastRowNum();//获取行数
            for( int rows=2;rows<=rowNum;rows++){
                Row row = sheet.getRow(rows);//取得某一行   对象
                T entity = buildEntity(clazz,row,mappingMap);
                if(entity!=null){
                    list.add(entity);
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }finally {
            if(inputStream!=null){
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }
    //合并成map
    private Map<String,String> mappingMergeToMap(String mapping){
        Map<String,String> map = new HashMap<String,String>();
        if(StringUtils.isBlank(mapping)){
            return map;
        }
        try{
            List<Map<String,String>> list = (List<Map<String,String>>)JSON.parse(mapping);
            if(ListUtils.isNotBlank(list)){
                for(int i=0;i<list.size();i++){
                    Map<String,String> itemMap = list.get(i);
                    map.putAll(itemMap);
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return map;
    }
    //反转一下
    private Map<String,String> mappingReverse(String mapping){
        Map<String,String> map = new HashMap<String,String>();
        if(StringUtils.isBlank(mapping)){
            return map;
        }
        try {
            List<Map<String,String>> list = (List<Map<String,String>>)JSON.parse(mapping);
            if(ListUtils.isNotBlank(list)){
                for(int i=0;i<list.size();i++){
                    Map<String,String> itemMap = list.get(i);
                    Set<String> keySet = itemMap.keySet();
                    for(String key:keySet){
                        map.put(itemMap.get(key),key);
                    }
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return map;
    }
    //把表头第二行转换成对象 便于下面cell数据恢复
    private  Map<Integer,Map<String,String>> buildMappingReverseMap(String[] mappingArr){
        Map<Integer,Map<String,String>> mappingMap = new HashMap<Integer,Map<String,String>>();
        for(int i=0;i<mappingArr.length;i++){
            mappingMap.put(i,mappingReverse(mappingArr[i]));
        }
        return mappingMap;
    }
    //把表头第二行转换成对象 便于下面cell数据恢复
    private  Map<Integer,Map<String,String>> buildMappingMergeMap(String[] mappingArr){
        Map<Integer,Map<String,String>> mappingMap = new HashMap<Integer,Map<String,String>>();
        for(int i=0;i<mappingArr.length;i++){
            mappingMap.put(i,mappingMergeToMap(mappingArr[i]));
        }
        return mappingMap;
    }

    private <T> T buildEntity(Class<T> clazz,Row row,Map<Integer,Map<String,String>> mappingMap ) throws IllegalAccessException, InstantiationException {
        if(row==null){
            return null;
        }
        ReflectUtil reflectUtil = new ReflectUtil();
        T obj = clazz.newInstance();
        Field[] fields= clazz.getDeclaredFields();
        for(int j=0;j<fields.length;j++){
            Field field = fields[j];
            Cell cell = row.getCell(j);
            if(cell==null){
                continue;
            }
            field.setAccessible(true);
            Map<String,String> mappingArrMap = mappingMap.get(j);
            String fieldName = field.getName();
            String type = field.getType().getSimpleName();
            if(type.equals("Date")){
                String magicalValue = cell.getStringCellValue();//表格展示的数据
                reflectUtil.setBeanValue(obj,Date.class,fieldName,StringUtils.isNotBlank(magicalValue)?DateFormatUtils.getDate(magicalValue,"yyyy-MM-dd HH:mm:ss"):null);
            }else if(type.equals("Boolean") || type.equals("boolean")){
                String magicalValue = cell.getStringCellValue();//表格展示的数据
                String dbValue = mappingArrMap!=null?mappingArrMap.get(magicalValue):null;//数据库真实数据 使用mapping映射回去的
                if(dbValue!=null){
                    reflectUtil.setBeanValue(obj,Boolean.class,fieldName,StringUtils.isNotBlank(dbValue)?new Boolean(dbValue):null);
                }else {
                    reflectUtil.setBeanValue(obj,Boolean.class,fieldName,StringUtils.isNotBlank(magicalValue)?new Boolean(magicalValue):null);
                }
            }else if(type.equals("Long")|| type.equals("long")){
                String magicalValue = getCellValue(cell,type);//表格展示的数据
                String dbValue = mappingArrMap!=null?mappingArrMap.get(magicalValue):null;//数据库真实数据 使用mapping映射回去的
                if(dbValue!=null){
                    reflectUtil.setBeanValue(obj,Long.class,fieldName,StringUtils.isNotBlank(dbValue)?Long.valueOf(dbValue):null);
                }else {
                    reflectUtil.setBeanValue(obj,Long.class,fieldName,StringUtils.isNotBlank(magicalValue)?Long.valueOf(magicalValue):null);
                }
            }else if(type.equals("Integer")|| type.equals("int")){
                String magicalValue = getCellValue(cell,type);//表格展示的数据
                String dbValue = mappingArrMap!=null?mappingArrMap.get(magicalValue):null;//数据库真实数据 使用mapping映射回去的
                if(dbValue!=null){
                    reflectUtil.setBeanValue(obj,Integer.class,fieldName,StringUtils.isNotBlank(dbValue)?Integer.valueOf(dbValue):null);
                }else {
                    reflectUtil.setBeanValue(obj,Integer.class,fieldName,StringUtils.isNotBlank(magicalValue)?Integer.valueOf(magicalValue):null);
                }
            }else if(type.equals("Double")|| type.equals("double")){
                String magicalValue = getCellValue(cell,type);//表格展示的数据
                String dbValue = mappingArrMap!=null?mappingArrMap.get(magicalValue):null;//数据库真实数据 使用mapping映射回去的
                if(dbValue!=null){
                    reflectUtil.setBeanValue(obj,Double.class,fieldName,StringUtils.isNotBlank(dbValue)?Double.valueOf(dbValue):null);
                }else {
                    reflectUtil.setBeanValue(obj,Double.class,fieldName,StringUtils.isNotBlank(magicalValue)?Double.valueOf(magicalValue):null);
                }
            }else if(type.equals("Float")|| type.equals("float")){
                String magicalValue = getCellValue(cell,type);//表格展示的数据
                String dbValue = mappingArrMap!=null?mappingArrMap.get(magicalValue):null;//数据库真实数据 使用mapping映射回去的
                if(dbValue!=null){
                    reflectUtil.setBeanValue(obj,Float.class,fieldName,StringUtils.isNotBlank(dbValue)?Float.valueOf(dbValue):null);
                }else {
                    reflectUtil.setBeanValue(obj,Float.class,fieldName,StringUtils.isNotBlank(magicalValue)?Float.valueOf(magicalValue):null);
                }
            }else if(type.equals("BigDecimal")){
                String magicalValue = getCellValue(cell,type);//表格展示的数据
                String dbValue = mappingArrMap!=null?mappingArrMap.get(magicalValue):null;//数据库真实数据 使用mapping映射回去的
                if(dbValue!=null){
                    reflectUtil.setBeanValue(obj,BigDecimal.class,fieldName,StringUtils.isNotBlank(dbValue)?new BigDecimal(dbValue):null);
                }else {
                    reflectUtil.setBeanValue(obj,BigDecimal.class,fieldName,StringUtils.isNotBlank(magicalValue)?new BigDecimal(magicalValue):null);
                }
            }else {
                String magicalValue = getCellValue(cell,type);//表格展示的数据
                String dbValue = mappingArrMap!=null?mappingArrMap.get(magicalValue):null;//数据库真实数据 使用mapping映射回去的
                if(dbValue!=null){
                    reflectUtil.setBeanValue(obj,String.class,fieldName,dbValue);
                }else {
                    reflectUtil.setBeanValue(obj,String.class,fieldName,magicalValue);
                }
            }
        }
        return obj;
    }

    @Deprecated
    public static void writeExcelToTempFile(List pageList, String[] titles, File tmpFile) {
        FileOutputStream out = null;
        SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
        Sheet sh = wb.createSheet();
        try {
            //头部
            Row headRow = sh.createRow(0);
            for(int i=0;i<titles.length;i++){
                Cell cell = headRow.createCell(i);
                cell.setCellValue(titles[i]);
            }
            //数据
            for(int i=1;i<=pageList.size();i++){
                Object obj = pageList.get(i-1);
                Row row = sh.createRow(i);
                Field[] fields=obj.getClass().getDeclaredFields();
                for(int j=0;j<fields.length;j++){
                    Field v = fields[j];
                    Cell cell = row.createCell(j);
                    v.setAccessible(true);
                    Object va = v.get(obj);
                    String showValue;
                    if(va != null){
                        if(v.getType() == Date.class){
                            showValue = DateFormatUtils.getStringDate((Date)va,"yyyy-MM-dd HH:mm:ss");
                            cell.setCellValue(showValue);
                        }else if(v.getType() == Boolean.class){
                            if((Boolean) va){
                                showValue = "是";
                            }else {
                                showValue = "否";
                            }
                            cell.setCellValue(showValue);
                        }else if(v.getType() == Long.class){
                            cell.setCellValue((Long)va);
                        }else if(v.getType() == Integer.class){
                            cell.setCellValue((Integer)va);
                        }else if(v.getType() == Double.class){
                            cell.setCellValue((Double)va);
                        }else if(v.getType() == Float.class){
                            cell.setCellValue((Float)va);
                        }else if(v.getType() == BigDecimal.class){
                            cell.setCellValue(((BigDecimal)va).floatValue());
                        }else {
                            showValue = va+"";
                            cell.setCellValue(showValue);
                        }
                    }
                }
            }
            out = new FileOutputStream(tmpFile);
            wb.write(out);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(out!=null){
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            if(wb!=null){
                wb.dispose();
            }
        }
    }

    /**
     * 根据excel单元格类型获取excel单元格值
     * @param cell
     * @return
     */
    private String getCellValue(Cell cell,String type) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
                // 如果当前Cell的Type为NUMERIC
                case Cell.CELL_TYPE_NUMERIC: {
                     if(type.equals("Long")|| type.equals("long")){
                        return String.valueOf((long)cell.getNumericCellValue());//表格展示的数据
                    }else if(type.equals("Integer")|| type.equals("int")){
                        return String.valueOf((int)cell.getNumericCellValue());//表格展示的数据
                    }else{
                        return String.valueOf(cell.getNumericCellValue());//表格展示的数据
                    }
                }
                // 如果当前Cell的Type为STRIN
                case Cell.CELL_TYPE_STRING:
                    // 取得当前的Cell字符串
                    cellvalue = cell.getStringCellValue();
                    break;
                case  Cell.CELL_TYPE_BLANK:
                    cellvalue = null;
                    break;
                // 默认的Cell值
                default:{
                    cellvalue = null;
                }
            }
        } else {
            cellvalue = "";
        }
        if(StringUtils.isBlank(cellvalue)){
            return null;
        }
        return cellvalue;
    }
}
